package com.tianlan.blog.dao;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.tianlan.blog.dto.LogInfo;
import com.tianlan.blog.dto.QueryLog;
import com.tianlan.blog.entity.SysLog;
import com.tianlan.common.page.MyPage;

public interface SysLogDao extends JpaRepository<SysLog, Long>{
	
	@Query(value = "SELECT DISTINCT A.id AS id, A.method_name AS methodName, A.in_params AS inParams, A.out_params AS outParams, "
			+ "A.log_type AS logType, A.operation_name AS operationName, A.operation_ip AS operationIp, A.create_time AS createTime, "
			+ "A.creator AS creator "
			+ "FROM sys_log A INNER JOIN sys_user B ON A.creator = B.id AND B.del_flag = '0' "
			+ "WHERE A.del_flag = '0' "
			+ "AND (A.log_type = :#{#log.logType} OR :#{#log.logType} is null OR :#{#log.logType} = '') "
//			+ "AND (A.operation_name like concat('%', :#{#log.operationName}, '%') OR :#{#log.operationName} is null OR :#{#log.operationName} = '') "
			+ "AND (DATE_FORMAT(A.create_time,'%Y-%m-%d') >= DATE_FORMAT(:#{#log.startTime},'%Y-%m-%d') or :#{#log.startTime} is null) "
			+ "AND (DATE_FORMAT(A.create_time,'%Y-%m-%d') <= DATE_FORMAT(:#{#log.endTime},'%Y-%m-%d') or :#{#log.endTime} is null) "
			+ "ORDER BY A.create_time DESC LIMIT :#{#page.start},:#{#page.size}"
	,nativeQuery = true)
	List<Object[]> queryLogList(@Param("log") QueryLog log,@Param("page") MyPage<LogInfo> page);
	
	@Query(value = "SELECT COUNT(1) "
			+ "FROM sys_log A INNER JOIN sys_user B ON A.creator = B.id AND B.del_flag = '0' "
			+ "WHERE A.del_flag = '0' "
//			+ "AND (A.method_name like concat('%', :#{#log.methodName}, '%') OR :#{#log.methodName} is null OR :#{#log.methodName} = '') "
			+ "AND (A.log_type = :#{#log.logType} OR :#{#log.logType} is null OR :#{#log.logType} = '') "
//			+ "AND (B.user_name like concat('%', :#{#log.creator}, '%') OR :#{#log.creator} is null OR :#{#log.creator} = '') "
			+ "AND (DATE_FORMAT(A.create_time,'%Y-%m-%d') >= DATE_FORMAT(:#{#log.startTime},'%Y-%m-%d') or :#{#log.startTime} is null) "
			+ "AND (DATE_FORMAT(A.create_time,'%Y-%m-%d') <= DATE_FORMAT(:#{#log.endTime},'%Y-%m-%d') or :#{#log.endTime} is null) "
	,nativeQuery = true)
	int queryLogCount(@Param("log") QueryLog log);
}
